﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;

namespace j1
{
    public class ClassDataAccess
    {
        public static void AddClass(Class cls)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "INSERT INTO Class (Name, SchoolId) VALUES (@name, @schoolId)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", cls.Name);
                    command.Parameters.AddWithValue("@schoolId", cls.SchoolId);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Added Class: {cls.Name} to SchoolId: {cls.SchoolId}");
        }

        public static void DeleteClass(int classId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "DELETE FROM Class WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", classId);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Deleted Class with Id: {classId}");
        }

        public static void UpdateClass(Class cls)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "UPDATE Class SET Name = @name, SchoolId = @schoolId WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", cls.Name);
                    command.Parameters.AddWithValue("@schoolId", cls.SchoolId);
                    command.Parameters.AddWithValue("@id", cls.Id);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Updated Class with Id: {cls.Id}");
        }

        public static Class GetClass(int classId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM Class WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", classId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Class
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            };
                        }
                    }
                }
            }
            return null;
        }

        public static List<Class> GetAllClasses()
        {
            var classes = new List<Class>();
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM Class";
                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classes.Add(new Class
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            });
                        }
                    }
                }
            }
            return classes;
        }
    }

}
